package com.huiyuanai.pojo.user;

import com.huiyuanai.dao.User;
import com.huiyuanai.util.BaseUtil;
import com.mysql.cj.util.StringUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: liwan
 * @Description:
 * @Date: create in 2021/10/4
 */
public class UserPojoImpl implements UserPojo {
    @Override
    public User logIn(Connection connection, String userCode) throws SQLException {
        //这些都是传过去的值
        PreparedStatement prepareStatement = null;
        ResultSet resultSet = null;
        User user = null;
        //判断一下是否为空
        if (connection != null) {
            String sqlCode = "select * from smbms_user where userCode = ?";
            String[] param = {userCode};
            resultSet = BaseUtil.execute(connection, sqlCode, param, resultSet, prepareStatement);
            user = new User();
            while (resultSet.next()) {
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("createdBy"));
                user.setCreationDate(resultSet.getDate("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getDate("modifyDate"));
            }
        }

        //释放资源
        BaseUtil.clostResour(null, prepareStatement, resultSet);

        return user;
    }

    @Override
    public int updataPWD(Connection connection, String pwd, int id) throws SQLException {
        //这些都是传过去的值
        PreparedStatement prepareStatement = null;
        int ans = 0;
        //判断一下是否为空
        if (connection != null) {
            String sqlCode = "update smbms_user set userPassword = ? where id = ?";
            Object[] param = {pwd, id};
            ans = BaseUtil.execute(connection, sqlCode, param, prepareStatement);
        }

        //释放资源
        BaseUtil.clostResour(null, prepareStatement, null);
        return ans;
    }

    @Override
    public int userCount(Connection connection, String userName, int role) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> list = new ArrayList<Object>();
        int con = 0;
        if (connection != null) {
            String sql = "select count(1) as count from smbms_user u, smbms_role r where u.userRole = r.id";
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql += " and u.userName like ?";
                list.add("%" + userName + "%");
            }
            if (role > 0) {
                sql += " and u.userRole = ?";
                list.add(role);
            }
            resultSet = BaseUtil.execute(connection, sql, list.toArray(), resultSet, preparedStatement);
            while (resultSet.next()) {
                con = resultSet.getInt("count");
            }
            BaseUtil.clostResour(null, preparedStatement, resultSet);
        }
        return con;
    }

    @Override
    public List<User> getUserList(Connection connection, String userName, int role, int openPage, int countPage, String userCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        List<Object> list = new ArrayList<Object>();
        List<User> userList = new ArrayList<User>();
        if (connection != null) {
            String sql = "select u.*, r.roleName from smbms_user u, smbms_role r where u.userRole = r.id";
            if (!StringUtils.isNullOrEmpty(userName)) {
                sql += " and u.userName like ?";
                list.add("%" + userName + "%");
            }
            if (role > 0) {
                sql += " and u.userRole = ?";
                list.add(role);
            }
            System.out.println("userCode" + userCode);
            if(!StringUtils.isNullOrEmpty(userCode)){
                sql += " and u.userCode = ?";
                list.add(userCode);
            }
            if (openPage > 0 && countPage > 0) {
                //order by creationDate DESC
                sql += " limit ? , ?";
                int currentPage = (openPage - 1) * countPage;
                list.add(currentPage);
                list.add(countPage);
            }
            resultSet = BaseUtil.execute(connection, sql, list.toArray(), resultSet, preparedStatement);
            while (resultSet.next()) {
                User user = new User();
                user.setId(resultSet.getInt("id"));
                user.setUserCode(resultSet.getString("userCode"));
                user.setUserName(resultSet.getString("userName"));
                user.setUserPassword(resultSet.getString("userPassword"));
                user.setGender(resultSet.getInt("gender"));
                user.setBirthday(resultSet.getDate("birthday"));
                user.setPhone(resultSet.getString("phone"));
                user.setAddress(resultSet.getString("address"));
                user.setUserRole(resultSet.getInt("userRole"));
                user.setCreatedBy(resultSet.getInt("createdBy"));
                user.setCreationDate(resultSet.getDate("creationDate"));
                user.setModifyBy(resultSet.getInt("modifyBy"));
                user.setModifyDate(resultSet.getDate("modifyDate"));
                user.setUserRoleName(resultSet.getString("roleName"));
                userList.add(user);
            }
        }
        BaseUtil.clostResour(null, preparedStatement, resultSet);

        return userList;
    }

    @Override
    public int addUser(Connection connection, User user) throws SQLException {
        //传参准备
        PreparedStatement preparedStatement = null;
        List<Object> list = new ArrayList<Object>();
        int result = 0;
        if (connection != null) {
            String sql = "insert into smbms_user (userCode, userName, userPassword, gender, birthday, phone, address, userRole, createdBy, creationDate) values (?,?,?,?,?,?,?,?,?,?)";
            list.add(user.getUserCode());
            list.add(user.getUserName());
            list.add(user.getUserPassword());
            list.add(user.getGender());
            list.add(user.getBirthday());
            list.add(user.getAddress());
            list.add(user.getPhone());
            list.add(user.getUserRole());
            list.add(user.getCreatedBy());
            list.add(user.getCreationDate());
            result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
        }
        BaseUtil.clostResour(connection, preparedStatement, null);
        return result;
    }

    @Override
    public int isUserCode(Connection connection, String userCode) throws SQLException {
        PreparedStatement preparedStatement = null;
        List<Object> list = new ArrayList<Object>();
        ResultSet resultSet = null;
        int result = 0;
        if (connection != null) {
            String sql = "select count(1) as count from smbms_user where userCode = ?";
            list.add(userCode);
            resultSet = BaseUtil.execute(connection, sql, list.toArray(), resultSet, preparedStatement);
            while (resultSet.next()) {
                result = resultSet.getInt("count");
            }
        }
        BaseUtil.clostResour(null, preparedStatement, resultSet);
        return result;
    }

    @Override
    public int userDel(Connection connection, String userID) throws SQLException {
        //传参需要的变量
        PreparedStatement preparedStatement = null;
        int result = 0;
        List<Object> list = new ArrayList<Object>();
        if (connection != null) {
            String sql = "delete from smbms_user where id = ?";
            list.add(userID);
            result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
        }
        BaseUtil.clostResour(null, preparedStatement, null);
        return result;
    }

    @Override
    public int updataUser(Connection connection, User user) {
        PreparedStatement preparedStatement = null;
        List<Object> list = new ArrayList<Object>();
        int result = 0;
        if (connection != null && user != null) {
            String sql = "update smbms_user set userName = ?, gender = ?, birthday = ?, phone = ?, address = ?, userRole = ? where id = ?";
            list.add(user.getUserName());
            list.add(user.getGender());
            list.add(user.getBirthday());
            list.add(user.getPhone());
            list.add(user.getAddress());
            list.add(user.getUserRole());
            list.add(user.getId());
            try {
                result = BaseUtil.execute(connection, sql, list.toArray(), preparedStatement);
            } catch (SQLException throwables) {
                throwables.printStackTrace();
            }
        }
        BaseUtil.clostResour(null, preparedStatement, null);
        return result;
    }
}
